/**
 * @ClassName: UserAccountDetailDao
 * @author 831
 * @date 2016骞?10鏈?21鏃?
 */
package com.fzrskj.cloud.free.user.dao;

import com.fzrskj.cloud.free.core.utils.AgentDataRangeUtils;
import com.fzrskj.cloud.free.core.utils.CloudContextUtils;
import com.fzrskj.cloud.free.core.utils.CloudDbDrvUtils;
import com.fzrskj.cloud.free.user.bean.UserAccountDetail;
import com.fzrskj.cloud.free.user.bean.UserAccountDetailView;
import ms.core.consts.SysErr;
import ms.core.db.IDbDrv;
import ms.core.object.CObject;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserAccountDetailDao extends CObject {
    //private declarations
    private IDbDrv db = null;

    //constructor function to initialize database access object
    public UserAccountDetailDao() {
        db = CloudDbDrvUtils.getDbUtilsDrv();
    }

    //to free resource
    @Override
    public void dispose() {
        db.closeConnection();
    }

    //to add new record to database by bean
    public int addUserAccountdetail(UserAccountDetail obj) {
        if (null == obj.getAgentId()) {
            obj.setAgentId(CloudContextUtils.getAgentId());
        }
        String sql = "insert into user_accountdetail(id,amount,balance,bankCode,billno,itemId," +
                "operator,regDate,remark,snId,status,userId," +
                "userName,voucherId,agentId)" +
                "values(?,?,?,?,?,?," +
                "?,?,?,?,?,?," +
                "?,?,?);";
        Object[] prm = {obj.getId(), obj.getAmount(), obj.getBalance(), obj.getBankCode(), obj.getBillno(), obj.getItemId(),
                obj.getOperator(), obj.getRegDate(), obj.getRemark(), obj.getSnId(), obj.getStatus(), obj.getUserId(),
                obj.getUserName(), obj.getVoucherId(), obj.getAgentId()};
        int ret = db.executeSql(sql, prm);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to add new record to database by map
    public int addUserAccountdetail(Map<String, Object> values) {
        //if (!values.containsKey("agentId")) {
            values.put("agentId", CloudContextUtils.getAgentId());
        //}
        int ret = db.insertRecord("user_accountdetail", values);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to delete table record by id
    public int delUserAccountdetail(int id) {
        String sql = "delete from user_accountdetail where id=? and agentId=?";
        int ret = db.executeSql(sql, new Object[]{id, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to delete table record by cdt
    public int delUserAccountdetail(Map<String, Object> cdt) {
        int ret = db.deleteRecord("user_accountdetail", cdt);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    public UserAccountDetailView queryUadView(Map<String, Object> cdt) {
        return queryUadView(cdt, false);
    }

    //to query user_accountdetail item for detail
    public UserAccountDetailView queryUadView(Map<String, Object> cdt, Boolean withoutAgent) {
        StringBuilder sb = new StringBuilder("select u.*,");
        sb.append("(select dpm.description from dic_paramitem dpm where paramId in (select paramEName from dic_paramindex where paramEName='financeItem'  and paramType='0')  and  dpm.value = u.itemId ) as itemIdName ");
        sb.append("from user_accountdetail u where 1=1 ");

        List<Object> list = new ArrayList<>();

        if (!withoutAgent) {
            sb.append(" and u.agentId=? ");
            list.add(CloudContextUtils.getAgentId());
        }

        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            sb.append(" and " + c.getKey() + "=?");
            list.add(c.getValue());
        }

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }

        UserAccountDetailView ret = db.queryForBean(sb.toString(), UserAccountDetailView.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    public UserAccountDetailView queryUadViewWithoutAgentId(Map<String, Object> cdt) {
        return queryUadView(cdt, true);
    }

    //to query UserAccountdetail record count
    public int queryUserAccountDetail(Map<String, Object> cdt) {
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select count(id) from user_accountdetail where 1=1 and agentId=?");

        list.add(CloudContextUtils.getAgentId());

        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sb.append(" and regDate >=?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sb.append(" and regDate <?");
                list.add(c.getValue());
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }

        Long ret = (Long) db.queryFieldValue(sb.toString(), Long.class, params, 1);
        this.setErrCode(db.getErrCode());
        return ret.intValue();
    }

    //to query UserAccountdetail for record list with condition,if page or pagesize less than 0, means without page
    public List<UserAccountDetail> queryUserAccountdetailList(Map<String, Object> cdt, int page, int pagesize) {
        List<Object> tmp = new ArrayList<>();
        String sql = "select * from user_accountdetail where 1>0 and agentId=?";

        tmp.add(CloudContextUtils.getAgentId());

        if (cdt.get("start") != null) {
            sql += " and regDate>=?";
            tmp.add(cdt.get("start"));
        }

        if (cdt.get("end") != null) {
            sql += " and regDate<?";
            tmp.add(cdt.get("end"));
        }

        if (cdt.get("subject") != null) {
            sql += " and itemId=?";
            tmp.add(cdt.get("subject"));
        }

        sql += " order by regDate Desc";
        if (page > 0 && pagesize > 0) {
            sql += String.format(" limit %d,%d", (page - 1) * pagesize, pagesize);
        }

        Object[] params = new Object[tmp.size()];
        for (int i = 0; i < tmp.size(); i++) {
            params[i] = tmp.get(i);
        }

        List<UserAccountDetail> rets = db.queryForBeans(sql, UserAccountDetail.class, params);
        this.setErrCode(db.getErrCode());
        return rets;
    }

    public List<UserAccountDetailView> queryUserAccountdetailViewList(Map<String, Object> cdt, int page, int pagesize) {
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select u.*,");
        sb.append("(select dpm.description from dic_paramitem dpm where paramId in (select paramEName from dic_paramindex where paramEName='financeItem'  and paramType='0')  and  dpm.value = u.itemId ) as itemIdName");
        sb.append(" from user_accountdetail u where 1>0 and u.agentId=? ");

        list.add(CloudContextUtils.getAgentId());

        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sb.append(" and regDate >=?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sb.append(" and regDate <=?");
                list.add(c.getValue());
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }
        sb.append(" order by regDate Desc");
        if (page > 0 && pagesize > 0) {
            sb.append(String.format(" limit %d,%d", (page - 1) * pagesize, pagesize));
        }
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<UserAccountDetailView> rets = db.queryForBeans(sb.toString(), UserAccountDetailView.class, params);
        this.setErrCode(db.getErrCode());
        return rets;
    }

    //to query user_accountdetail item for Amount
    public double queryAccountDetailAmount(Map<String, Object> cdt) {
        List<Object> list = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select  ifnull(sum(amount),0) from user_accountdetail where 1=1 and agentId=? ");

        list.add(CloudContextUtils.getAgentId());

        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sql.append(" and regDate >=?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sql.append(" and regDate <=?");
                list.add(c.getValue());
            } else {
                sql.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        Double total = (Double) db.queryFieldValue(sql.toString(), Float.class, params, 1);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        if (total < 0 || total == null)
            return 0;
        return total.doubleValue();
    }

    //收入统计(真实入款-业务退款)
    public double queryAccountIncome(Map<String, Object> cdt) {
        List<Object> list = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select ifnull(sum(amount),0)-(select IFNULL(sum(amount),0) ");
        sql.append("from user_accountdetail where itemId=600 and agentId=? ");
        list.add(CloudContextUtils.getAgentId());
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sql.append(" and regDate >=?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sql.append(" and regDate <?");
                list.add(c.getValue());
            } else {
                sql.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        sql.append(") from user_accountdetail where 1=1 and itemId=100 and agentId=?");
        list.add(CloudContextUtils.getAgentId());
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sql.append(" and regDate >=?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sql.append(" and regDate <?");
                list.add(c.getValue());
            } else {
                sql.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        //代理数据隔离
        AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sql);

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }

        Double total = (Double) db.queryFieldValue(sql.toString(), Float.class, params, 1);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        if (total < 0 || total == null)
            return 0;
        return total.doubleValue();
    }

    /**
     * 根据用户名获得可开票金额
     *
     * @param uname
     * @return
     */
    public double getAllSureInvoice(String uname) {
        int agentId = CloudContextUtils.getAgentId();
        Object params[] = {uname, agentId, uname, agentId, uname, agentId, uname, agentId};
        StringBuilder sb = new StringBuilder("select IFNULL(sum(amount),0)-");
        sb.append("(select IFNULL(sum(amount),0) from user_accountdetail where itemId=200 and userName=? and agentId=? )-");
        sb.append("(select IFNULL(sum(amount),0) from user_accountdetail where itemId=600 and userName=? and agentId=?)-");
        sb.append("(select IFNULL(sum(price),0) from user_invoice where userName=? and agentId=?) ");
        sb.append("from user_accountdetail where itemId=100 and userName=? and agentId=?");
        Double ret = (Double) db.queryFieldValue(sb.toString(), Double.class, params, 1);
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
            ret = (double) -1;
        } else {
            if (ret == null || ret < 0) {
                ret = 0.0;
            }
        }
        return ret.doubleValue();
    }

    /**
     *
     * @param orderId
     * @return
     */
    public List<UserAccountDetail> getOrderHistory(String orderId) {
        //不能加agentID
        String sql = "select * from user_accountdetail where voucherId=? and itemId=300";
        List<UserAccountDetail> history = this.db.queryForBeans(sql, UserAccountDetail.class, new Object[] {orderId});
        String code = db.getErrCode();
        this.setErrCode(code);
        return history;
    }

    /**
     * 查询订单对应的退账记录.
     * @param orderId
     * @param userId
     * @return
     */
    public UserAccountDetail getOrderBackFinance(String orderId, Integer userId) {
        //不能加agentID
        String sql = "select * from user_accountdetail where voucherId=? and itemId=400 and userId=?";
        UserAccountDetail detail = this.db.queryForBean(sql, UserAccountDetail.class, new Object[]{userId, orderId});
        String code = db.getErrCode();
        this.setErrCode(code);
        return detail;
    }
}